import pool from '../config/database.js';

export const DiningModel = {
  async getAllMenuItems() {
    try {
      const [rows] = await pool.query(`
        SELECT 
          id,
          DATE_FORMAT(date, '%Y-%m-%d') as date,
          meal_type as mealType,
          name,
          nutrition,
          special_diet as specialDiet,
          status,
          created_at as createdAt,
          updated_at as updatedAt
        FROM dining_menu 
        ORDER BY date, meal_type
      `);
      return rows;
    } catch (error) {
      console.error('Error in getAllMenuItems:', error);
      throw new Error('Failed to fetch menu items');
    }
  },

  async createMenuItem(item) {
    try {
      console.log('Creating menu item with data:', item);
      // 转换日期格式
      const date = item.date.split('T')[0];

      const [result] = await pool.query(
        `INSERT INTO dining_menu (
          date, 
          meal_type, 
          name, 
          nutrition, 
          special_diet, 
          status
        ) VALUES (?, ?, ?, ?, ?, ?)`,
        [
          date,
          item.mealType,
          item.name,
          item.nutrition,
          item.specialDiet,
          item.status || '计划中'
        ]
      );
      return result.insertId;
    } catch (error) {
      console.error('Error in createMenuItem:', error);
      throw new Error('Failed to create menu item');
    }
  },

  async updateMenuItem(id, item) {
    try {
      console.log('Updating menu item with data:', { id, item });
      // 检查记录是否存在
      const [existing] = await pool.query('SELECT * FROM dining_menu WHERE id = ?', [id]);
      if (!existing || existing.length === 0) {
        throw new Error(`Menu item with id ${id} not found`);
      }

      // 转换日期格式
      const date = item.date.split('T')[0];

      const [result] = await pool.query(
        `UPDATE dining_menu 
        SET 
          date = ?, 
          meal_type = ?, 
          name = ?, 
          nutrition = ?, 
          special_diet = ?, 
          status = ?,
          updated_at = CURRENT_TIMESTAMP
        WHERE id = ?`,
        [
          date,
          item.mealType,
          item.name,
          item.nutrition,
          item.specialDiet,
          item.status,
          id
        ]
      );

      if (result.affectedRows === 0) {
        throw new Error('No records were updated');
      }

      return true;
    } catch (error) {
      console.error('Error in updateMenuItem:', error);
      throw new Error(`Failed to update menu item: ${error.message}`);
    }
  },

  async getNutritionAnalysis() {
    try {
      const [rows] = await pool.query(`
        SELECT 
          DATE_FORMAT(date, '%Y-%m-%d') as date,
          COUNT(*) as totalMeals,
          AVG(CAST(REGEXP_SUBSTR(nutrition, '热量: ([0-9]+)', 1, 1) AS UNSIGNED)) as avgCalories,
          AVG(CAST(REGEXP_SUBSTR(nutrition, '蛋白质: ([0-9]+)g', 1, 1) AS UNSIGNED)) as avgProtein,
          AVG(CAST(REGEXP_SUBSTR(nutrition, '碳水: ([0-9]+)g', 1, 1) AS UNSIGNED)) as avgCarbs,
          AVG(CAST(REGEXP_SUBSTR(nutrition, '脂肪: ([0-9]+)g', 1, 1) AS UNSIGNED)) as avgFat
        FROM dining_menu
        WHERE date >= DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY)
        GROUP BY date
        ORDER BY date DESC
      `);
      return rows;
    } catch (error) {
      console.error('Error in getNutritionAnalysis:', error);
      throw new Error('Failed to get nutrition analysis');
    }
  }
};